using System;
using System.Configuration;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.Globalization;
using System.Net;
using System.Net.Mail;
using System.Net.Mime;
using System.IO;
using System.IO.Compression;
using System.Runtime.InteropServices;
using System.Security;
using System.Security.Cryptography;
using System.Security.Principal;
using System.Text;
using System.Text.RegularExpressions;
using System.Timers;
using System.Web;
using System.Web.Mail;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;

namespace Halide
{
	/// <summary>
	/// The Halide.Database class contains methods and properties for performing operations on,
	/// or evaluating, database tables.
	/// </summary>
	/// <example>
	/// Add the following to your web.config file to use the Halide Data class with SQL Server.
	/// <code lang="XML">
	///		&lt;connectionStrings&gt;
	///			&lt;add name="Halide" connectionString="Data Source=server; Initial Catalog=database; User ID=username; Password=password; Connection Timeout=30;" providerName="System.Data.SqlClient" /&gt;
	///		&lt;/connectionStrings&gt;
	/// </code>
	/// </example>
	sealed public class Database
	{
		private static string _version = "2008.10.15A";

		/// <summary>
		/// Return the version number of the class; read-only.
		/// </summary>
		/// <value>
		/// A string with the version number of the class.
		/// </value>
		public static string Version
		{
			get
			{
				return _version;
			}
		}

		private static Exception _lastSqlError;

		/// <summary>
		/// Retrieve the last SQL error that occurred.
		/// </summary>
		/// <returns>Last SQL Exception</returns>
		public static Exception SqlLastError()
		{
			return _lastSqlError;
		}

		/// <summary>
		/// No constructor is necessary.
		/// </summary>
		private Database()
	    {
	    }

		/// <summary>
		///	The SQL connection string, retrieved from the web.config file.
		/// </summary>
		/// <example>
		/// Add the following to your web.config file to use the Halide Data class.
		/// <code lang="XML">
		///		&lt;connectionStrings&gt;
		///			&lt;add name="Halide" connectionString="Data Source=server; Initial Catalog=database; User ID=username; Password=password; Connection Timeout=30;" providerName="System.Data.SqlClient" /&gt;
		///		&lt;/connectionStrings&gt;
		/// </code>
		/// </example>
		/// <value>
		/// A connection string from the web.config file.
		/// </value>
	    public static string SqlConnectionString
	    {
			get
			{
				string CS = "";

				try
				{
					CS = ConfigurationManager.ConnectionStrings["Halide"].ConnectionString;
				}

				catch
				{
					if (String.IsNullOrEmpty(CS))
					{
						CS = "";
					}
				}

				return CS;
			}
	    }

	    /// <summary>
	    /// Look up a value from a database object. Returns only one string
		/// value, so query must only return one value.
	    /// </summary>
	    /// <param name="sql">SQL query string for which to return a string value.</param>
		/// <param name="ConnectionStringName">Name of the connection string in the web.config file.</param>
		/// <returns>A string value returned from the query.</returns>
	    public static string SqlLookup(string sql, string ConnectionStringName)
	    {
	        string ret = "";

			try
			{
				string conStr = SqlConnectionString;

				if (!String.IsNullOrEmpty(ConnectionStringName))
				{
					conStr = ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString;
				}

				using (SqlConnection cn = new SqlConnection(conStr))
				{
					cn.Open();

					using (SqlCommand oc = new SqlCommand(sql, cn))
					{
						Object retval = oc.ExecuteScalar();
						oc.Dispose();

						if (retval != null)
						{
							ret = retval.ToString();
						}
					}

					cn.Close();
				}
			}

			catch (Exception err)
			{
				ret = err.ToString();
			}

	        return ret;
	    }

		/// <summary>a
	    /// Look up a value from a database object. Returns only one string
		/// value, so query must only return one value.
	    /// </summary>
	    /// <param name="sql">SQL query string for which to return a string value.</param>
		/// <returns>A string value returned from the query.</returns>
		public static string SqlLookup(string sql)
		{
			return (SqlLookup(sql, ""));
		}

		/// <summary>
        /// Get names and SQL data types for stored procedure input parameters.
		/// </summary>
		/// <example>
		/// <code>
		/// string[,] result = Halide.Database.SqlGetSpParams("spu_ProcedureName");
		/// </code>
		/// </example>
		/// <param name="spName">Name of the stored procedure.</param>
		/// <returns>String array [x, 2] of input parameter names and SQL data types.</returns>
		public static string[,] SqlGetSpParams(string spName)
		{
			return SqlGetSpParams(spName, "Halide");
		}

		/// <summary>
        /// Get names and SQL data types for stored procedure input parameters.
		/// </summary>
		/// <example>
		/// <code>
		/// string[,] result = Halide.Database.SqlGetSpParams("spu_ProcedureName", "ConnectionName");
		/// </code>
		/// </example>
		/// <param name="spName">Name of the stored procedure.</param>
		/// <param name="ConnectionStringName">Name of connection string in the Web.Config file. Defaults to "Halide".</param>
		/// <returns>String array [x, 2] of input parameter names and SQL data types.</returns>
		public static string[,] SqlGetSpParams(string spName, string ConnectionStringName)
		{
			SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString);
			SqlCommand cmd = new SqlCommand(spName, con);
			cmd.CommandType = CommandType.StoredProcedure;
			con.Open();
			SqlCommandBuilder.DeriveParameters(cmd);

			int count = 0;

			foreach (SqlParameter parameter in cmd.Parameters)
				if (parameter.Direction == ParameterDirection.Input) count++;

			string[,] retVal = new string[count, 2];

			count = 0;

			foreach (SqlParameter parameter in cmd.Parameters)
			{
				if (parameter.Direction == ParameterDirection.Input)
				{
					retVal[count, 0] = parameter.ParameterName;
					retVal[count++, 1] = parameter.SqlDbType.ToString();
				}
			}

			cmd.Dispose();
			con.Close();

			return retVal;
		}

		/// <summary>
		/// Execute a SQL command string.
		/// </summary>
		/// <example>
		/// <code>
		/// bool result = Halide.Database.SqlExec("exec spu_ProcedureName @param1=0, @param2='test string', @param2='joe''s club';");
		/// </code>
		/// </example>
		/// <param name="statement">SQL statement to execute.</param>
		/// <param name="ConnectionString">Name of the connection string in the web.config file.</param>
		/// <returns>true if successful, false if failure.</returns>
		public static bool SqlExec(string statement, string ConnectionString)
		{
            try
            {
				string conStr = SqlConnectionString;

				if (!String.IsNullOrEmpty(ConnectionString))
				{
					conStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString;
				}

                using (SqlConnection cn = new SqlConnection(conStr))
                {
                    cn.Open();

                    using (SqlCommand oc = new SqlCommand(statement, cn))
                    {
                        oc.ExecuteNonQuery();
                    }

                }
                
                return true;
			}

			catch (Exception err)
			{
				_lastSqlError = err;
				return false;
			}
		}

		/// <summary>
		/// Execute a SQL command string.
		/// </summary>
		/// <example>
		/// <code>
		/// bool result = Halide.Database.SqlExec("exec spu_ProcedureName @param1=0, @param2='test string', @param2='joe''s club';");
		/// </code>
		/// </example>
		/// <param name="statement">SQL statement to execute.</param>
		/// <returns>true if successful, false if failure.</returns>
		public static bool SqlExec(string statement)
		{
			return (SqlExec(statement, ""));
		}

/*
		/// <summary>
	    /// Execute a SQL stored procedure string.
	    /// </summary>
		/// <example>
		/// <code>
		/// bool result = Halide.Database.SqlExec("exec spu_ProcedureName @param1=0, @param2='test string', @param2='joe''s club';");
		/// </code>
		/// </example>
		/// <param name="statement">SQL statement to execute.</param>
		/// <returns>true if successful, false if failure.</returns>
	    public static bool SqlExec(string statement)
	    {
			bool retVal = true;
			string sp = statement;
			string spParams = "";

			if (sp.IndexOf("exec ") == 0)
				sp = sp.Replace("exec ", "").Trim();

			if (sp.IndexOf(" ") > 0)
			{
				spParams = sp.Substring(sp.IndexOf(" ") + 1, sp.Length - sp.IndexOf(" ") - 1).TrimEnd(';');
				sp = sp.Substring(0, sp.IndexOf(" "));
			}

			SqlConnection con = new SqlConnection(Halide.Database.SqlConnectionString);
			SqlCommand cmd = new SqlCommand(sp, con);
			cmd.CommandType = CommandType.StoredProcedure;

			int added;

			Regex tags = new Regex(@"([ |,]{1,})(@[A-Za-z0-9_-]{1,}=)", RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Singleline);
			spParams = tags.Replace(spParams, "#HALIDE_COMMA#$2");

			string[] stringSeparators = new string[] { "#HALIDE_COMMA#" };
			string[] stringSeparators2 = new string[] { "#HALIDE_EQ#" };

			if (spParams.Length > 0)
			{
				string[] array = spParams.Split(stringSeparators, StringSplitOptions.RemoveEmptyEntries);

				for (int x = 0; x < array.Length; x++)
				{
					int firstEQ = array[x].IndexOf("=");

					if (firstEQ > 0 && firstEQ < array[x].Length)
					{
						string str = array[x];
						array[x] = str.Substring(0, firstEQ) + "#HALIDE_EQ#" + str.Substring(firstEQ + 1, str.Length - firstEQ - 1);

						string[] ar = array[x].Split(stringSeparators2, StringSplitOptions.RemoveEmptyEntries);

						ar[0] = ar[0].Trim();
						ar[0] = ar[0].Trim(Convert.ToChar("@"));
						ar[1] = ar[1].Trim();
						ar[1] = ar[1].Trim(Convert.ToChar("'"));
						ar[1] = ar[1].Replace("''", "'");

						cmd.Parameters.AddWithValue(ar[0], ar[1]);
					}
				}
			}

			try
			{
				con.Open();
				added = cmd.ExecuteNonQuery();
			}
			catch
			{
				retVal = false;
			}
			finally
			{
				cmd.Dispose();
				con.Close();
			}

			return retVal;
		}
*/

	    /// <summary>
		/// INSERT or UPDATE SQL table using injection-safe method.
		/// Specify field variables as F1, F2, F3, etc. in order of appearance in string array.
	    /// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname" };
		/// string result = Halide.Database.SqlWrite("INSERT INTO signups (first_name, last_name) VALUES (@F1, @F2)", fields);
		/// </code>
		/// </example>
		/// <param name="sql">SQL statement to execute.</param>
	    /// <param name="fields">string array with field names from submitted form.</param>
		/// <returns>"SUCCESS"" if successful or error code upon failure.</returns>
	    public static string SqlWrite(string sql, string[] fields)
		{
			if(fields.Length < 1)
			{
				return ("No fields were specified for the SQL statement to process.");
			}
			else
			{
				SqlConnection con = new SqlConnection(Halide.Database.SqlConnectionString);
				SqlCommand cmd = new SqlCommand(sql, con);

				for(int counter = 1; counter <= fields.Length; counter++)
				{
					cmd.Parameters.AddWithValue("@F" + counter.ToString(), Halide.HtmlForms.RequestParam(fields[counter - 1]));
				}

				int added = 0;

				try
				{
					con.Open();
					added = cmd.ExecuteNonQuery();
				}
				catch (Exception err)
				{
					_lastSqlError = err;
					return (err.ToString());
				}
				finally
				{
					cmd.Dispose();
					con.Close();
				}

				return ("SUCCESS");
			}
		}

		/// <summary>
		/// Used by the SqlSpExec method, et al. Determines where form data
		/// resides when passing values to a stored procedure. Various other
		/// methods exist to transfer submitted form data into Session
		/// variables or Context Items, requiring this parameter.
		/// </summary>
		public enum FormSource
		{
			/// <summary>Form data was submitted via a simple POST operation.</summary>
			FormFields,

			/// <summary>Form data resides in Session variables.</summary>
			SessionVariables,

			/// <summary>Form data resides in Context items.</summary>
			ContextItems,

			/// <summary>Ignore this parameter, values already passed by argument.</summary>
			Ignore
		};

	    /// <summary>
		/// Execute a stored procedure with passed form variables.
		/// Specify field variables string array. Field names and SP variable names must match!
		/// Can use RequestToContext() or RequestToSessions() to automatically build the string
		/// array of field names.
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname" };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields);
		/// </code>
		/// </example>
	    /// <param name="sp">Name of stored procedure.</param>
	    /// <param name="fields">string array with field names from submitted form.</param>
		/// <returns>"SUCCESS" if successful or error code upon failure.</returns>
	    public static string SqlSpExec(string sp, string[] fields)
		{
			return SqlSpExec(sp, fields, FormSource.FormFields);
		}

		/// <summary>
		/// Execute a stored procedure with passed form variables.
		/// Specify field variables string array. Field names and SP variable names must match!
		/// Can use RequestToContext() or RequestToSessions() to automatically build the string
		/// array of field names.
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname" };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields, true);
		/// </code>
		/// </example>
		/// <param name="sp">Name of stored procedure.</param>
		/// <param name="fields">string array with field names from submitted form.</param>
		/// <param name="ReturnValue">"true" to return any value from the stored procedure,
		/// "false" to return "SUCCESS" or error message.</param>
		/// <returns>See "ReturnValue" property.</returns>
		public static string SqlSpExec(string sp, string[] fields, bool ReturnValue)
		{
			return SqlSpExec(sp, fields, null, FormSource.FormFields, ReturnValue, "Halide");
		}

        /// <summary>
		/// <para>Execute a stored procedure with passed form variables and optional value overrides.
        /// Specify field variables string array. Field names and SP variable names must match!
        /// Can use RequestToContext() or RequestToSessions() to automatically build the string
        /// array of field names.</para>
        /// <para>Value overrides ca be used when field values cannot be returned as simple Request
        /// objects (eg. DatePicker, Select, etc.) Leave string array elements blank to pull the
		/// values from the Request object, or set as the value for the object.</para>
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname", "email1", "date", "age", "gender" };
		/// string[] values = new string[] { "", "", "", DatePicker.SelectedDate.ToString(), "", "" };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields, values);
        /// </code>
		/// </example>
        /// <param name="sp">Name of stored procedure.</param>
        /// <param name="fields">string array with field names from submitted form.</param>
        /// <param name="values">string array with field value overrides. If using server-side controls, values must be specified for all fields.</param>
        /// <returns>"SUCCESS" if successful or error code upon failure.</returns>
        public static string SqlSpExec(string sp, string[] fields, string[] values)
        {
            return SqlSpExec(sp, fields, values, FormSource.FormFields);
        }

		/// <summary>
		/// <para>Execute a stored procedure with passed form variables and optional value overrides.
		/// Specify field variables string array. Field names and SP variable names must match!
		/// Can use RequestToContext() or RequestToSessions() to automatically build the string
		/// array of field names.</para>
		/// <para>Value overrides ca be used when field values cannot be returned as simple Request
		/// objects (eg. DatePicker, Select, etc.) Leave string array elements blank to pull the
		/// values from the Request object, or set as the value for the object.</para>
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname", "email1", "date", "age", "gender" };
		/// string[] values = new string[] { "", "", "", DatePicker.SelectedDate.ToString(), "", "" };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields, values);
		/// </code>
		/// </example>
		/// <param name="sp">Name of stored procedure.</param>
		/// <param name="fields">string array with field names from submitted form.</param>
		/// <param name="values">string array with field value overrides. If using server-side controls, values must be specified for all fields.</param>
		/// <param name="ConnectionStringName">Name of the connection string within the web.config file.</param>
		/// <returns>"SUCCESS" if successful or error code upon failure.</returns>
		public static string SqlSpExec(string sp, string[] fields, string[] values, string ConnectionStringName)
		{
			return SqlSpExec(sp, fields, values, FormSource.FormFields, true, ConnectionStringName);
		}

	    /// <summary>
		/// Execute a stored procedure with passed form field names in string array.
		///	Can optionally use Session[] variables or COntext.Items[] to enable pre-processing of form
		/// data prior to sending to SQL stored procedure. Field/Session/Context names and SP variable
		/// names must match! Can use RequestToContext() or RequestToSessions() to automatically build
		/// the string array of field names.
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname", "email1" };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields);
		/// </code>
		///	Optionally, use sessions and dynamically retrieve form fields:
		///	<code>
		///	string[] fields = Halide.HtmlForms.RequestToSessions();
		///	string result = Halide.Database.SqlSpExec("spu_AddPost", fields, Halide.Database.FormSource.SessionVariables);
	    /// </code>
		/// </example>
	    /// <param name="sp">Name of stored procedure.</param>
	    /// <param name="fields">string array with field names from submitted form.</param>
	    /// <param name="useSessions">FormSource enumeration value.</param>
		/// <returns>"SUCCESS" if successful or error code upon failure.</returns>
        public static string SqlSpExec(string sp, string[] fields, FormSource useSessions)
        {
            return SqlSpExec(sp, fields, null, useSessions);
        }

		/// <summary>
		/// Execute a stored procedure with passed data field names in string array.
		///	Can optionally use Session[] variables or COntext.Items[] to enable pre-processing of form
		/// data prior to sending to SQL stored procedure. Field/Session/Context names and SP variable
		/// names must match! Can use RequestToContext() or RequestToSessions() to automatically build
		/// the string array of field names.
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname", "email1" };
		/// string[] values = new string[] { "", "", value5 };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields, values);
		/// </code>
		///	Optionally, use sessions and dynamically retrieve form fields:
		///	<code>
		///	string[] fields = Halide.HtmlForms.RequestToSessions();
		///	string result = Halide.Database.SqlSpExec("spu_AddPost", fields, Halide.Database.FormSource.SessionVariables);
		///	</code>
	    /// </example>
	    /// <param name="sp">Name of stored procedure.</param>
	    /// <param name="fields">string array with field names from submitted form.</param>
        /// <param name="values">string array with field value overrides. If using server-side controls, values must be specified for all fields.</param>
        /// <param name="useSessions">FormSource enumeration value.</param>
		/// <returns>"SUCCESS" if successful or error code upon failure.</returns>
		public static string SqlSpExec(string sp, string[] fields, string[] values, FormSource useSessions)
		{
			return SqlSpExec(sp, fields, values, useSessions, false, "Halide");
		}

	    /// <summary>
		/// Execute a stored procedure with passed form field names in string array.
		///	Can optionally use Session[] variables or Context.Items[] to enable pre-processing of form
		/// data prior to sending to SQL stored procedure. Field/Session/Context names and SP variable
		/// names must match! Can use RequestToContext() or RequestToSessions() to automatically build
		/// the string array of field names.
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname", "email1" };
		/// string[] values = new string[] { "", "", value5 };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields, values, Halide.Database.FormSource.FormFields, true);
		/// </code>
		///	Optionally, use sessions and dynamically retrieve form fields:
		///	<code>
		///	string[] fields = Halide.HtmlForms.RequestToSessions();
		///	string result = Halide.Database.SqlSpExec("spu_AddPost", fields, Halide.Database.FormSource.SessionVariables);
		///	</code>
	    /// </example>
	    /// <param name="sp">Name of stored procedure.</param>
	    /// <param name="fields">string array with field names from submitted form.</param>
        /// <param name="values">string array with field value overrides. If using server-side controls, values must be specified for all fields.</param>
        /// <param name="useSessions">FormSource enumeration value.</param>
		/// <param name="ReturnValues">Return any column value that may exist.</param>
		/// <returns>"SUCCESS" if successful or error code upon failure if ReturnValues is false.
		/// Returns the result of the stored procedure if successful or error code upon failure if ReturnValues is true.</returns>
		public static string SqlSpExec(string sp, string[] fields, string[] values, FormSource useSessions, bool ReturnValues)
		{
			return SqlSpExec(sp, fields, values, useSessions, ReturnValues, "Halide");
		}

	    /// <summary>
		/// Execute a stored procedure with passed form field names in string array.
		///	Can optionally use Session[] variables or Context.Items[] to enable pre-processing of form
		/// data prior to sending to SQL stored procedure. Field/Session/Context names and SP variable
		/// names must match! Can use RequestToContext() or RequestToSessions() to automatically build
		/// the string array of field names.
		/// </summary>
		/// <example>
		/// <code>
		/// string[] fields = new string[] { "fname", "lname", "email1" };
		/// string[] values = new string[] { "", "", value5 };
		/// string result = Halide.Database.SqlSpExec("spu_Function_Name", fields, values, Halide.Database.FormSource.FormFields, true, "Halide");
		/// </code>
		///	Optionally, use sessions and dynamically retrieve form fields:
		///	<code>
		///	string[] fields = Halide.HtmlForms.RequestToSessions();
		///	string result = Halide.Database.SqlSpExec("spu_AddPost", fields, Halide.Database.FormSource.SessionVariables);
		///	</code>
	    /// </example>
	    /// <param name="sp">Name of stored procedure.</param>
	    /// <param name="fields">string array with field names from submitted form.</param>
        /// <param name="values">string array with field value overrides. If using server-side controls, values must be specified for all fields.</param>
        /// <param name="useSessions">FormSource enumeration value.</param>
		/// <param name="ReturnValues">Return any column value that may exist.</param>
		/// <param name="ConnectionStringName">Name of connection string to use within the web.config file.</param>
		/// <returns>"SUCCESS" if successful or error code upon failure if ReturnValues is false.
		/// Returns the result of the stored procedure if successful or error code upon failure if ReturnValues is true.</returns>
	    public static string SqlSpExec(string sp, string[] fields, string[] values, FormSource useSessions, bool ReturnValues, string ConnectionStringName)
		{
			string sql = sp;
			string added = "";

			if(fields.Length < 1 || string.IsNullOrEmpty(sp))
			{
				return ("No fields were specified for the SQL stored procedure to process, and/or no stored procedure name was specified.");
			}
			else
			{
				try
				{
					SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString);
					SqlCommand cmd = new SqlCommand(sql, con);
					cmd.CommandType = CommandType.StoredProcedure;

					for(int counter = 0; counter < fields.Length; counter++)
					{
						string value = String.Empty;
						
						if (useSessions == FormSource.FormFields)
						{
							if (HttpContext.Current.Request.Form[fields[counter]] != null)
							{
								value = HttpContext.Current.Request.Form[fields[counter]].ToString();
							}
						}

						if (useSessions == FormSource.SessionVariables)
						{
							if (HttpContext.Current.Session[fields[counter]] != null)
							{
								value = HttpContext.Current.Session[fields[counter]].ToString();
							}
						}

						if (useSessions == FormSource.ContextItems)
						{
							if (HttpContext.Current.Items[fields[counter]] != null)
							{
								value = HttpContext.Current.Items[fields[counter]].ToString();
							}
						}

						if (!String.IsNullOrEmpty(values[counter]))
						{
							value = values[counter];
						}

						cmd.Parameters.AddWithValue("@" + fields[counter], value);
					}

					try
					{
						con.Open();

						if (ReturnValues)
						{
							added = cmd.ExecuteScalar().ToString();
						}

						else
						{
							cmd.ExecuteNonQuery().ToString();
							added = "SUCCESS";
						}
					}

					catch (Exception err)
					{
						_lastSqlError = err;
						return (err.ToString());
					}

					finally
					{
						cmd.Dispose();
						con.Close();
					}

				}

				catch (Exception err)
				{
					_lastSqlError = err;
					return (err.ToString());
				}

				return (added);
			}
		}

	    /// <summary>
	    /// Loads the results of a SQL query into a DataTable object.
	    /// </summary>
		/// <example>
		/// <code>
		/// DataTable dt = Halide.Database.SqlReadTable("SELECT TOP 5 FROM tablename;");
		/// </code>
		/// </example>
	    /// <param name="sqlcmd">SQL statement to execute.</param>
		/// <returns>DataTable object.</returns>
	    public static DataTable SqlReadTable(string sqlcmd)
	    {
	        return (SmartReader.ReadTable(sqlcmd));
	    }

	    /// <summary>
	    /// Sanitizes the string for SQL use by converting apostrophes to
		/// double apostrophes.
	    /// </summary>
		/// <example>
		/// <code>
		/// string cleaned = Halide.Database.SqlClean(strVal);
		/// </code>
		/// </example>
		/// <param name="data">String to filter.</param>
		/// <returns>Passed string value after filtering.</returns>
	    public static string SqlClean(string data)
	    {
	        return data.Replace("'", "''");
	    }

	    /// <summary>
	    /// Look up a value from an OleDb database. Returns only one string
		/// value, so query must only return one value.
	    /// </summary>
		/// <example>
		/// <code>
		/// string value = Halide.Database.OleDbLookup("SELECT TOP 5 FROM tablename", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\myPath\myJet.mdb; User ID=[user name]; Password=[password]");
		/// </code>
		/// </example>
		/// <param name="sql">SQL query string for which to return string value</param>
	    /// <param name="connString">OleDb connection string</param>
		/// <returns>A string</returns>
	    public static string OleDbLookup(string sql, string connString)
	    {
	        string ret;

	        try
	        {
	            using (OleDbConnection cn = new OleDbConnection(connString))
	            {
	                cn.Open();

	                using (OleDbCommand oc = new OleDbCommand(sql, cn))
	                {
	                    ret = oc.ExecuteScalar().ToString();
	                }
	            }
			}

			catch (Exception err)
			{
				_lastSqlError = err;
				ret = null;
			}
			
			return ret;
	    }

	    /// <summary>
	    /// Execute an OleDb command.
	    /// </summary>
		/// <example>
		/// <code>
		/// bool value = Halide.Database.OleDbExec("DELETE FROM tablename", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\myPath\myJet.mdb; User ID=[user name]; Password=[password]");
		/// </code>
		/// </example>
		/// <param name="statement">SQL statement to execute.</param>
	    /// <param name="connString">OleDb connection string.</param>
		/// <returns>true if successful, false if failure.</returns>
	    public static bool OleDbExec(string statement, string connString)
	    {
	       	try
	        {
		        using (OleDbConnection con = new OleDbConnection())
		        {
		            con.ConnectionString = connString;
		            con.Open();

		            using (OleDbCommand cmd = new OleDbCommand(statement, con))
		            {
		                cmd.ExecuteNonQuery();
		                return true;
		            }
		        }
			}

			catch (Exception err)
			{
				_lastSqlError = err;
				return false;
			}
		}

	// Outer Identify class
	}

// Outer namespace
}